package com.mago.tis.daoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.mago.tis.dao.ITransportDao;
import com.mago.tis.pojo.Tismbt;
import com.mago.tis.pojo.Tismct;
import com.mago.tis.utility.ApplicationConstant;
import com.mago.tis.utility.ApplicationException;
import com.mago.tis.utility.CollectionUtil;
import com.mago.tis.utility.DBConstant;
import com.mago.tis.utility.ExceptionMessage;
import com.mago.tis.utility.QueryConstant;

public class TransportDao extends BaseDao implements ITransportDao {

	private static final Logger logger = Logger.getLogger(TransportDao.class);

	/**
	 * Method that saves the Excel data
	 */
	public int saveExcel(List<List<String>> excelDataList)
			throws ApplicationException {
		int[] batchUpdatedRows = null;
		try {
			Connection con = getConnection();

			// Set Auto commit to false
			con.setAutoCommit(false);
			PreparedStatement pstmt = con
					.prepareStatement(QueryConstant.SAVE_MASTER_DATA);

			if (!CollectionUtil.isEmptyList(excelDataList)) {
				// int pk = getPrimaryKey("MASTER_ID", "TISMCT");

				long startTime = System.currentTimeMillis();
				// Add data in a batch that will be executed later

				for (int count = 0; count < excelDataList.size(); count++) {

					List<String> data = excelDataList.get(count);

					if ((!CollectionUtil.isEmptyList(data))) {
						// If row record is of invalid length, skip to the
						// new record
						if (data.size() < ApplicationConstant.MASTER_SHEET_COLUMN_COUNT) {
							logger.info("Record" + (count + 1) + "Skipped");
							logger.info("SLIP NO :" + data.get(0));
							continue;
						} else {
							// Add each parameter to the row.
							pstmt.setString(1, data.get(0));
							pstmt.setString(2, data.get(1));
							pstmt.setString(3, data.get(2));
							pstmt.setString(4, data.get(3));
							pstmt.setString(5, data.get(4));
							pstmt.setString(6, data.get(5));
							//pstmt.setString(7, null);
							pstmt.setString(7, data.get(6));
							pstmt.setString(8, data.get(7));
							pstmt.setString(9, data.get(8));
							pstmt.setString(10, data.get(9));
							pstmt.setString(11, data.get(10));
							//pstmt.setString(12, null);
							pstmt.setString(12, data.get(11));
							pstmt.setString(13, data.get(12));
							pstmt.setString(14, data.get(13));
							pstmt.setString(15, data.get(14));
							pstmt.setString(16, data.get(15));
							pstmt.setString(17, data.get(16));
							pstmt.setString(18, data.get(17));
							pstmt.setString(19, data.get(18));
							pstmt.setString(20, data.get(19));
							pstmt.setString(21, data.get(20));
							pstmt.setString(22, data.get(21));
							pstmt.setString(23, data.get(22));
							pstmt.setString(24, data.get(23));
							pstmt.setString(25, data.get(24));
							pstmt.setString(26, data.get(25));
							//pstmt.setString(27, null);
							pstmt.setString(27, data.get(26));
							pstmt.setString(28, data.get(27));
							pstmt.setString(29, data.get(28));
							pstmt.setString(30, data.get(29));
							pstmt.setString(31, data.get(30));
							//pstmt.setString(32, null);
							pstmt.setString(32, data.get(31));
							pstmt.setString(33, data.get(32));
							pstmt.setString(34, data.get(33));
							pstmt.setString(35, data.get(34));
							pstmt.setString(36, DBConstant.IS_BILLED_NO);
							pstmt.setString(37, DBConstant.IS_ACTIVE_YES);
							pstmt.setDate(38,
									new Date(System.currentTimeMillis()));
							pstmt.setDate(39,
									new Date(System.currentTimeMillis()));
							pstmt.setString(40, null);
							pstmt.setString(41, null);
							pstmt.setString(42, null);
							pstmt.setString(43, "Piyush");
							pstmt.setString(44, "Piyush");

							// Add row to the batch.
							pstmt.addBatch();
						}
					}
				}

				try {
					// Batch is ready, execute it to insert the data
					batchUpdatedRows = pstmt.executeBatch();

					// Commit the transaction
					con.commit();
				} catch (SQLException sqe) {
					logger.error("TransportDao --->saveExcel(): Batch Update Prob : "
							+ sqe.getMessage());
					throw sqe;
				} finally {
					pstmt.close();
					// con.close();
				}
				long endTime = System.currentTimeMillis();
				long elapsedTime = (endTime - startTime) / 1000;
				logger.info("Time taken for Excel Save:" + elapsedTime);
			}

		} catch (SQLException sqe) {
			logger.error("TransportDao --->saveExcel(): " + sqe.getMessage());
			throw new ApplicationException(
					ApplicationConstant.MASTER_DATA_SAVE_FAIL);
		}

		// Return total rows affected
		return batchUpdatedRows.length;
	}

	/**
	 * Method to get the master data from the database
	 */
	public List<Tismct> getMasterData() throws ApplicationException {
		List<Tismct> masterDataList = new ArrayList<Tismct>();
		List paramList = new ArrayList();
		paramList.add(0, DBConstant.IS_BILLED_NO);
		paramList.add(1, DBConstant.IS_ACTIVE_YES);
		// Get the query by passing parameters
		String query = getQuery(QueryConstant.GET_MASTER_DATA, paramList);
		ResultSet rs = null;
		Tismct tismct = null;
		try {
			rs = getResults(query);
			if (rs != null) {
				while (rs.next()) {
					tismct = new Tismct();
					tismct.setBalRoyalityAmount(rs
							.getString("BAL_ROYALTY_AMOUNT"));
					tismct.setCleanEngCessAmount(rs
							.getString("CLEAN_ENG_CESS_AMOUNT"));
					tismct.setDumpLocation(rs.getString("DUMP_LOCATION"));
					tismct.setEduCessAmount(rs.getString("EDU_CESS_AMOUNT"));
					tismct.setHseCessAmount(rs.getString("HSECESS_AMOUNT"));
					tismct.setExciseAmount(rs.getString("EXCISE_AMOUNT"));
					tismct.setInvoiceNo(rs.getString("INVOICE_NO"));
					tismct.setMasterId(String.valueOf(rs.getInt("MASTER_ID")));
					tismct.setRoyalityAmount(rs.getString("ROYALTY_AMOUNT"));
					tismct.setRoyalityPassNo(rs.getString("ROYALTY_PASS_NO"));
					tismct.setRoyalityType(rs.getString("ROYALITY_TYPE"));
					tismct.setSlipNo(rs.getString("SLIP_NO"));
					tismct.setSubTransporterName(rs
							.getString("SUB_TRANSPORTER_NAME"));
					tismct.setVmsGrossWeight(rs.getString("VMS_GROSS_WEIGHT"));
					tismct.setVmsInDate(rs.getString("VMS_IN_DATE"));
					tismct.setVmsInTime(rs.getString("VMS_IN_TIME"));
					tismct.setVmsInWbNo(rs.getString("VMS_IN_WB_NO"));
					tismct.setVmsInWbUser(rs.getString("VMS_IN_WB_USER"));
					tismct.setVmsOutDate(rs.getString("VMS_OUT_DATE"));
					tismct.setVmsOutTime(rs.getString("VMS_OUT_TIME"));
					tismct.setVmsOutWbNo(rs.getString("VMS_OUT_WB_NO"));
					tismct.setVmsOutWbUser(rs.getString("VMS_OUT_WB_USER"));
					tismct.setVmsTareWeight(rs.getString("VMS_TARE_WEIGHT"));
					tismct.setVmsTotalWeight(rs.getString("VMS_NET_WEIGHT"));
					tismct.setVsGrossWeight(rs.getString("VS_GROSS_WEIGHT"));
					tismct.setVsInDate(rs.getString("VS_IN_DATE"));
					tismct.setVsInTime(rs.getString("VS_IN_TIME"));
					tismct.setVsInWbNo(rs.getString("VS_IN_WB_NO"));
					tismct.setVsInWbUser(rs.getString("VS_IN_WB_USER"));
					tismct.setVsNetWeight(rs.getString("VS_NET_WEIGHT"));
					tismct.setVsOutDate(rs.getString("VS_OUT_DATE"));
					tismct.setVsOutTime(rs.getString("VS_OUT_TIME"));
					tismct.setVsOutWbNo(rs.getString("VS_OUT_WB_NO"));
					tismct.setVsOutWbUser(rs.getString("VS_OUT_WB_USER"));
					tismct.setVsTareWeight(rs.getString("VS_TARE_WEIGHT"));
					tismct.setTruckNo(rs.getString("TRUCK_NO"));
					masterDataList.add(tismct);
				}
			}
			closeConnection();
		} catch (SQLException sqe) {
			logger.error("TransportDao --->getMasterData(): "
					+ sqe.getMessage());
			throw new ApplicationException(
					ApplicationConstant.MASTER_DATA_FETCH_ERROR);
		}
		return masterDataList;
	}

	/**
	 * Method to get the master data from the database
	 */
	public List<Tismct> getChallan1Data(String challanIds)
			throws ApplicationException {
		List<Tismct> challan1DataList = new ArrayList<Tismct>();
		List paramList = new ArrayList();
		paramList.add(0, DBConstant.IS_BILLED_NO);
		paramList.add(1, DBConstant.IS_ACTIVE_YES);
		// Get the query by passing parameters
		String query = getQuery(QueryConstant.GET_CHALLAN_1__DATA, paramList)
				+ challanIds + ") order by CREATED_DT desc";
		ResultSet rs = null;
		Tismct tismct = null;
		try {
			rs = getResults(query);
			if (rs != null) {
				while (rs.next()) {
					tismct = new Tismct();
					tismct.setMasterId(String.valueOf(rs.getInt("MASTER_ID")));
					tismct.setSlipNo(rs.getString("SLIP_NO"));
					tismct.setTruckNo(rs.getString("TRUCK_NO"));
					tismct.setVmsInDate(rs.getString("VMS_IN_DATE"));
					tismct.setVmsInTime(rs.getString("VMS_IN_TIME"));
					tismct.setVmsOutDate(rs.getString("VMS_OUT_DATE"));
					tismct.setSubTransporterName(rs.getString("SUB_TRANSPORTER_NAME"));
					tismct.setVmsInWbNo(rs.getString("VMS_IN_WB_NO"));
					
					tismct.setInvoiceNo(rs.getString("INVOICE_NO"));
					
					tismct.setRoyalityPassNo(rs.getString("ROYALTY_PASS_NO"));
					
					
					tismct.setVmsGrossWeight(rs.getString("VMS_GROSS_WEIGHT"));
					tismct.setVmsTotalWeight(rs.getString("VMS_NET_WEIGHT"));
					tismct.setVmsTareWeight(rs.getString("VMS_TARE_WEIGHT"));
					tismct.setVsInDate(rs.getString("VS_IN_DATE"));
					tismct.setVsOutDate(rs.getString("VS_OUT_DATE"));
					tismct.setVsGrossWeight(rs.getString("VS_GROSS_WEIGHT"));
					tismct.setVsNetWeight(rs.getString("VS_NET_WEIGHT"));
					tismct.setVsTareWeight(rs.getString("VS_TARE_WEIGHT"));
					tismct.setDumpLocation(rs.getString("DUMP_LOCATION"));
					challan1DataList.add(tismct);
				}
			}
			closeConnection();
		} catch (SQLException sqe) {
			logger.error("TransportDao --->getChallan1Data(): "
					+ sqe.getMessage());
			throw new ApplicationException(
					ApplicationConstant.CHALLAN_1_DATA_FETCH_ERROR);
		}
		return challan1DataList;
	}

	public List<Tismct> getBilledData() throws ApplicationException {
		List<Tismct> billedDataList = new ArrayList<Tismct>();
		List paramList = new ArrayList();
		paramList.add(0, DBConstant.IS_BILLED_YES);
		paramList.add(1, DBConstant.IS_ACTIVE_YES);
		// Get the query by passing parameters
		String query = getQuery(QueryConstant.GET_MASTER_DATA, paramList);
		ResultSet rs = null;
		Tismct tismct = null;
		try {
			rs = getResults(query);
			if (rs != null) {
				while (rs.next()) {
					tismct = new Tismct();
					tismct.setBalRoyalityAmount(rs
							.getString("BAL_ROYALTY_AMOUNT"));
					tismct.setCleanEngCessAmount(rs
							.getString("CLEAN_ENG_CESS_AMOUNT"));
					tismct.setDumpLocation(rs.getString("DUMP_LOCATION"));
					tismct.setEduCessAmount(rs.getString("EDU_CESS_AMOUNT"));
					tismct.setHseCessAmount(rs.getString("HSECESS_AMOUNT"));
					tismct.setExciseAmount(rs.getString("EXCISE_AMOUNT"));
					tismct.setInvoiceNo(rs.getString("INVOICE_NO"));
					tismct.setMasterId(String.valueOf(rs.getInt("MASTER_ID")));
					tismct.setRoyalityAmount(rs.getString("ROYALTY_AMOUNT"));
					tismct.setRoyalityPassNo(rs.getString("ROYALTY_PASS_NO"));
					tismct.setRoyalityType(rs.getString("ROYALITY_TYPE"));
					tismct.setSlipNo(rs.getString("SLIP_NO"));
					tismct.setSubTransporterName(rs
							.getString("SUB_TRANSPORTER_NAME"));
					tismct.setVmsGrossWeight(rs.getString("VMS_GROSS_WEIGHT"));
					tismct.setVmsInDate(rs.getString("VMS_IN_DATE"));
					tismct.setVmsInTime(rs.getString("VMS_IN_TIME"));
					tismct.setVmsInWbNo(rs.getString("VMS_IN_WB_NO"));
					tismct.setVmsInWbUser(rs.getString("VMS_IN_WB_USER"));
					tismct.setVmsOutDate(rs.getString("VMS_OUT_DATE"));
					tismct.setVmsOutTime(rs.getString("VMS_OUT_TIME"));
					tismct.setVmsOutWbNo(rs.getString("VMS_OUT_WB_NO"));
					tismct.setVmsOutWbUser(rs.getString("VMS_OUT_WB_USER"));
					tismct.setVmsTareWeight(rs.getString("VMS_TARE_WEIGHT"));
					tismct.setVmsTotalWeight(rs.getString("VMS_NET_WEIGHT"));
					tismct.setVsGrossWeight(rs.getString("VS_GROSS_WEIGHT"));
					tismct.setVsInDate(rs.getString("VS_IN_DATE"));
					tismct.setVsInTime(rs.getString("VS_IN_TIME"));
					tismct.setVsInWbNo(rs.getString("VS_IN_WB_NO"));
					tismct.setVsInWbUser(rs.getString("VS_IN_WB_USER"));
					tismct.setVsNetWeight(rs.getString("VS_NET_WEIGHT"));
					tismct.setVsOutDate(rs.getString("VS_OUT_DATE"));
					tismct.setVsOutTime(rs.getString("VS_OUT_TIME"));
					tismct.setVsOutWbNo(rs.getString("VS_OUT_WB_NO"));
					tismct.setVsOutWbUser(rs.getString("VS_OUT_WB_USER"));
					tismct.setVsTareWeight(rs.getString("VS_TARE_WEIGHT"));
					tismct.setTruckNo(rs.getString("TRUCK_NO"));
					billedDataList.add(tismct);
				}
			}
			closeConnection();
		} catch (SQLException sqe) {
			logger.error("TransportDao --->getMasterData(): "
					+ sqe.getMessage());
			throw new ApplicationException(
					ApplicationConstant.MASTER_DATA_FETCH_ERROR);
		}
		return billedDataList;

	}

	public int updateMasterData(String challanIds) throws ApplicationException {
		int rowsUpdated = 0;
		List<Tismct> challan1DataList = new ArrayList<Tismct>();
		List paramList = new ArrayList();
		paramList.add(0, DBConstant.IS_BILLED_YES);
		paramList.add(1, DBConstant.IS_ACTIVE_YES);
		// Get the query by passing parameters
		String query = getQuery(QueryConstant.UPDATE_MASTER_DATA_AFTER_BILLING,
				paramList) + challanIds + ")";
		try {
			rowsUpdated = update(query);
		} catch (SQLException sqe) {
			logger.error("TransportDao --->updateMasterData(): "
					+ sqe.getMessage());
			throw new ApplicationException(
					ApplicationConstant.MASTER_DATA_FETCH_ERROR);
		}
		return rowsUpdated;
	}
	
	/**
	 * Method to save the third challan data
	 * @param mbt
	 * @return
	 * @throws ApplicationException
	 */
	public boolean saveThirdChallan(Tismbt mbt) throws ApplicationException {

		// Prepare the parameters
		List lParamList = new ArrayList();
		boolean isSaved = false;
		int pk = 0;
		if (mbt != null) {
			try {
				pk = getPrimaryKey("MASTER_BILL_ID", "tismbt");
				lParamList.add(0, pk);
				lParamList.add(1, mbt.getTraderId());
				lParamList.add(2, mbt.getSlipNo());
				lParamList.add(3, mbt.getFinalDiff());
				lParamList.add(4, mbt.getGrossWeight());
				lParamList.add(5, mbt.getGrandTotal());
				lParamList.add(6, DBConstant.IS_ACTIVE_YES);
				lParamList.add(7, new Date(System.currentTimeMillis()));
				lParamList.add(8, new Date(System.currentTimeMillis()));
				lParamList.add(9, "Piyush");
				lParamList.add(10, "Piyush");
				lParamList.add(11, null);
				lParamList.add(12, null);
				lParamList.add(13, null);
				
				// Get the query by passing parameters
				String query = getQuery(QueryConstant.SAVE_FINAL_CHALLAN, lParamList);
				int resultCount = insert(query);
				if (resultCount != 0) {
					isSaved = true;
				} else
					isSaved = false;
				closeConnection();

			} catch (SQLException sqe) {
				throw new ApplicationException(
						ExceptionMessage.SAVE_CHALLAN_FAILED);
			}
		}
		return isSaved;
	}
}
